package hn.cch.framework.database;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;
import java.util.List;

public class TableUtil {

    private static final Logger logger = LoggerFactory.getLogger(TableUtil.class);


    public void updateTable(Connection connection, String updateSql) {
        logger.info("updateSql:" + updateSql);
        try (Statement statement = connection.createStatement()) {
            int updateRow = statement.executeUpdate(updateSql);
            logger.info("updateRow:" + updateRow);
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    /**
     * 批量插入
     *
     * @param connection
     * @param insertSql
     * @param paramsList
     */
    public void batchInsert(Connection connection, String insertSql, List<Object[]> paramsList) throws SQLException {
        logger.info("insertSql:" + insertSql);
        connection.setAutoCommit(false); // 关闭事务自动提交
        try (PreparedStatement preparedStatement = connection.prepareStatement(insertSql)) {
            for (Object[] params : paramsList) {
                for (int i = 0; i < params.length; i++) {
                    Object param = params[i];
                    if (param instanceof String) {
                        preparedStatement.setString(i, (String) param);
                    }
                    if (param instanceof Integer) {
                        preparedStatement.setInt(i, (Integer) param);
                    }
                    // todo ...
                    preparedStatement.addBatch();
                }
            }
            int[] insertRows = preparedStatement.executeBatch();
            logger.info("insertRows:" + Arrays.toString(insertRows));
            connection.commit(); // 提交事务
        } catch (SQLException e) {
            e.printStackTrace();
            connection.rollback(); // 回滚事务
        }
    }

    public void insertTable(Connection connection, String insertSql, Object[] params) {
        logger.info("insertSql:" + insertSql);
        try (PreparedStatement preparedStatement = connection.prepareStatement(insertSql)) {
            for (int i = 0; i < params.length; i++) {
                Object param = params[i];
                if (param instanceof String) {
                    preparedStatement.setString(i, (String) param);
                }
                if (param instanceof Integer) {
                    preparedStatement.setInt(i, (Integer) param);
                }
                // todo ...
            }
            int insertRow = preparedStatement.executeUpdate();
            logger.info("insertRow:" + insertRow);
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }


    public void selectTable(Connection connection, String selectSql) {
        logger.info("selectSql:" + selectSql);
        try (Statement statement = connection.createStatement()) {
            ResultSet resultSet = statement.executeQuery(selectSql);

            ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
            while (resultSet.next()) {
                resultSet.getObject(0);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

    }


    public void useMetaData(Connection connection, String tableName) {
        try {
            DatabaseMetaData databaseMetaData = connection.getMetaData();

            ResultSet tables = databaseMetaData.getTables(null, null, null, null);
            ResultSet columns = databaseMetaData.getColumns(null, null, tableName, null);
            ResultSet primaryKeys = databaseMetaData.getPrimaryKeys(null, null, tableName);

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
